<?php

/**
 * @version		$Id: hello.php 15 2009-11-02 18:37:15Z chdemko $
 * @package		Joomla16.Tutorials
 * @subpackage	Components
 * @copyright	Copyright (C) 2005 - 2010 Open Source Matters, Inc. All rights reserved.
 * @author		Christophe Demko
 * @link		http://joomlacode.org/gf/project/helloworld_1_6/
 * @license		License GNU General Public License version 2 or later
 */

// No direct access to this file
defined('_JEXEC') or die('Restricted access');

// import Joomla modelitem library
jimport('joomla.application.component.modelitem');

/**
 * HelloWorld Model
 */
class bgModelbg extends JModel
{  
    var $_total = null;
    var $_pagination = null;
    
    public function __construct(){
    	parent::__construct();
 
        $mainframe = JFactory::getApplication();
 
        // Get pagination request variables
        $limit = $mainframe->getUserStateFromRequest('global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
        $limitstart = JRequest::getVar('limitstart', 0, '', 'int');
 
        // In case limit has been changed, adjust it
        $limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
 
        $this->setState('limit', $limit);
        $this->setState('limitstart', $limitstart);
    }
    
    function getMaxId(){
        $this->_db->setQuery("SELECT MAGR_CODE FROM  magr ORDER BY MAGR_CODE DESC LIMIT 0 , 1");
        $max_id = $this->_db->loadObject();
        return $max_id->MAGR_CODE +1;        
    }
    
    function getArea(){
        $this->_db->setQuery("SELECT * FROM area ORDER BY AREA_CODE ASC");
        return $this->_db->loadObjectList();
    }
    
    function getPrefsByArea($area_code){
        $where = "";
        if($area_code != 'all'){
            $where = "WHERE PREF_AREA_CODE = $area_code";
        }
        $this->_db->setQuery("SELECT * FROM pref $where");             
        return $this->_db->loadObjectList();  
    }
    
    function getPrefs(){
        $this->_db->setQuery("SELECT * FROM pref");             
        return $this->_db->loadObjectList();  
    }
    
    
    function updateGroup($group) {
        $this->_db->setQuery("UPDATE magr 
                    SET MAGR_NAME = '$group[name]', MAGR_USERTYPE = $group[mode]
                    WHERE MAGR_CODE = $group[code]");
        $this->_db->query(); 
        
        switch ($group['mode']) {
            case 0 :
            case 1 :
            case 3 :
            case 4 :  
                //Clear old data before update
                $this->_db->setQuery("DELETE FROM deum WHERE DEUM_MAGR_CODE = $group[code]");
                $this->_db->query(); 
                                 
                foreach ( $group['data'] as $row ) {
                    $this->_db->setQuery("INSERT INTO deum ( DEUM_MAGR_CODE , DEUM_USEM_COCH_CODE ) 
                        VALUES ($group[code],$row)");      
                    $this->_db->query();        
                }
                break;
                            
            case 2 :
                //Clear old data before update
                $this->_db->setQuery("DELETE FROM dume WHERE DUME_MAGR_CODE = $group[code]");
                $this->_db->query(); 
                
                foreach ( $group['data'] as $row ) {
                    if(empty($row['email']))
                        continue;
                    $id = HelperBG::getIDAuto('dume','DUME_CODE');
                    $sql = "INSERT INTO dume ( DUME_CODE ,DUME_FULLNAME, DUME_MAIL, DUME_MAGR_CODE ) 
                        VALUES ($id, '$row[name]','$row[email]',$group[code])";
                    $this->_db->setQuery($sql);       
                    $this->_db->query();         
                }
                break;
        }
        
    }
    
    function addGroup($group) {
        $magr_id = HelperBG::getIDAuto('magr','MAGR_CODE');
        $this->_db->setQuery("INSERT INTO magr (MAGR_CODE,MAGR_NAME, MAGR_USERTYPE) VALUES ( $magr_id,'$group[name]', $group[mode] )");                
        $this->_db->query(); 

        switch ($group['mode']) {
            case 0 :
            case 1 :
            case 3 :
            case 4 :
                foreach($group['data'] as $row){
                    $id = HelperBG::getIDAuto('deum','DEUM_CODE');
                    $sql = "INSERT INTO deum ( DEUM_MAGR_CODE , DEUM_USEM_COCH_CODE ) 
                            VALUES ($magr_id,$row)";
                    $this->_db->setQuery($sql);       
                    $this->_db->query(); 
                }
                break;
            
            case 2 :
                foreach ( $group['data'] as $row ) {
                    if(empty($row['email']))
                        continue;
                    $id = HelperBG::getIDAuto('dume','DUME_CODE');
                    $sql = "INSERT INTO dume ( DUME_CODE ,DUME_FULLNAME, DUME_MAIL, DUME_MAGR_CODE ) 
                        VALUES ($id, '$row[name]','$row[email]',$magr_id)";
                    $this->_db->setQuery($sql);       
                    $this->_db->query();         
                }
                break;
        }
        
    }
    
    function searchUser($filter){
        $select = ""; 
        $where_area = empty($filter['area']) || $filter['area'] == 'all' ? "" : "AND AREA_CODE = $filter[area] ";
        $where_pref = empty($filter['pref']) || $filter['pref'] == 'all' ? "" : "AND PREF_CODE = $filter[pref] ";
        $where_keyword = $filter['keyword'] == "" ? "" : "AND ( OCBA_NAME LIKE '%$filter[keyword]%' OR OCBA_KANA LIKE '%$filter[keyword]%' )";
        $subSelect = "(SELECT DISTINCT OCBA_CODE, OCBA_NAME, OCBA_KANA, OCBA_TOWN_CODE, TOWN_PREF_CODE, PREF_CODE, PREF_AREA_CODE as AREA_CODE
                        FROM ocba
                        JOIN town ON OCBA_TOWN_CODE = TOWN_CODE
                        JOIN pref ON TOWN_PREF_CODE = PREF_CODE 
                        WHERE OCBA_FLAG = 1 $where_keyword)";
        
        switch ($filter['type']){
            case 0:
                $select = "SELECT id, name, email, OCBA_CODE, OCBA_NAME, OCBA_KANA, sendEmail as mail_flag FROM #__users JOIN ".$subSelect."AS ocba ON USEM_OCBA_CODE = OCBA_CODE WHERE 1 = 1 ";
                break;
            case 1:
                $select = "SELECT COCH_CODE as id, COCH_NAME as name, COCH_MAIL as email, COCH_MAIF as mail_flag, OCBA_CODE, OCBA_NAME, OCBA_KANA,
                    COCH_MAIK as m_email, COCH_MAKF as m_mail_flag, 
                    COCH_MAIW as w_email, COCH_MAFW as w_mail_flag
                FROM coch JOIN ".$subSelect."AS ocba ON COCH_OCBA_CODE = OCBA_CODE WHERE (COCH_MAIL <> '' OR COCH_MAIK <> ''  OR COCH_MAIW <> '') ";
                break;
            case 3:
                $select = "SELECT id, name, email, OCBA_CODE,OCBA_KANA, OCBA_NAME, sendEmail as mail_flag FROM #__users JOIN ".$subSelect."AS ocba ON USEM_OCBA_CODE = OCBA_CODE WHERE LEFT(OCBA_CODE,1)='0' ";
                break;
            case 4:
                $select = "SELECT id, name, email, OCBA_CODE, OCBA_NAME, OCBA_KANA, sendEmail as mail_flag FROM #__users JOIN ".$subSelect."AS ocba ON USEM_OCBA_CODE = OCBA_CODE WHERE LEFT(OCBA_CODE,1)='1' ";
                break;
            
        }

        
        $sql = $select . $where_area . $where_pref ;
        // echo $sql;
        // die;
        $this->_db->setQuery($sql);
        return $this->_db->loadObjectList();

    }
    
    function getGroup(){
        $keyword = JRequest::getVar('keyword');
        $where = "WHERE MAGR_CODE LIKE '%$keyword%' OR MAGR_NAME LIKE '%$keyword%'";
        $sql = "SELECT * FROM magr ";
        $this->_total = $this->getTotal($where);
       
        $sql .= $where;
       
        // order by
        $sql .= " ORDER BY MAGR_CODE DESC"; 
       
        // pagination
        $sql .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');
        $this->_db->setQuery($sql);       
        return $this->_db->loadObjectList();
    }   
    
    function getPagination(){
        // Load the content if it doesn't already exist
        if (empty($this->_pagination)) {
            jimport('joomla.html.pagination');
            $this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );
        }
        return $this->_pagination;
    }
    
    function getTotal($where = null){
        // Load the content if it doesn't already exist
        if ($this->_total === null) {
            $query = "SELECT count(*) FROM magr $where";
            $this->_db->setQuery($query);
            $this->_total = $this->_db->loadResult();
        }
        
        return $this->_total;
    }
    
    function getDetails($magr_code ){
        $this->_db->setQuery("SELECT MAGR_USERTYPE FROM magr WHERE MAGR_CODE = $magr_code");
               
        $obj = $this->_db->loadObject();
        $type =$obj->MAGR_USERTYPE;
        
        $sql="";
        switch($type){  
            case 0:
            case 3:
            case 4:
                $sql = "SELECT id, name, email FROM #__users 
                JOIN deum on id = DEUM_USEM_COCH_CODE 
                JOIN magr on DEUM_MAGR_CODE = MAGR_CODE";
                break;
                
            case 1:
                $sql = "SELECT COCH_CODE as id, COCH_NAME as name, 
                    COCH_MAIL as email, COCH_MAIF as mail_flag,
                    COCH_MAIK as m_email, COCH_MAKF as m_mail_flag, 
                    COCH_MAIW as w_email, COCH_MAFW as w_mail_flag
                FROM coch 
                JOIN deum on COCH_CODE = DEUM_USEM_COCH_CODE 
                JOIN magr on DEUM_MAGR_CODE = MAGR_CODE";
                break;
            
            case 2:
                $sql = "SELECT DUME_CODE as id, DUME_FULLNAME as name, DUME_MAIL as email FROM dume 
                JOIN magr on DUME_MAGR_CODE = MAGR_CODE";
                break;
        }
        $sql .=" WHERE MAGR_CODE = $magr_code";
        $this->_db->setQuery($sql);       
        return $this->_db->loadObjectList();
    } 
    
    function getGroupByCode($code){
        $sql ="SELECT * FROM magr WHERE MAGR_CODE = $code";
        $this->_db->setQuery($sql);       
        return $this->_db->loadObject();
    }
    
    function deleteGroup($list){
        foreach($list as $group_code){
            
            $this->_db->setQuery("DELETE FROM deum WHERE DEUM_MAGR_CODE = $group_code");
            $this->_db->query(); 
            
            $this->_db->setQuery("DELETE FROM dume WHERE DUME_MAGR_CODE = $group_code");
            $this->_db->query(); 
            
            $this->_db->setQuery("DELETE FROM magr WHERE MAGR_CODE = $group_code");
            $this->_db->query(); 
            
        }
    }
}
